/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package roomarrange;

import java.sql.Types;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.rowset.SqlRowSet;

/**
 *
 * @author ben
 */
public class DataJdbcDao extends JdbcDaoSupport implements DataDao {
    /*
    static final int bgs = 19; //办公室
    static final int jsk = 13; //技术科
    static final int kmr = 25; //开门人
    static final int fjd = 21; //法警队长
    static final int jcz = 26; //主管检察长
    static final int qclx = 27;//全程录像人员
    static final int zbfj = 31;//值班法警
    static final int ncfj = 32;//内勤法警
     */

    @Override
    public void initMeeting(String cid, String sdate, String edate, int roomid, int usrid, int iconfirmbygrpid, String app) {
        JdbcTemplate jt = getJdbcTemplate();
        String sStime, sEtime;
        sStime = sdate.substring(11, 16);
        sEtime = edate.substring(11, 16);
        String sTbName;
        int istatus = 0;
        Object[] params;
        int[] types;
        String sql;
        if (app.equalsIgnoreCase("room")) {
            sTbName = "t_meetings";
            sql = "insert into " + sTbName + " " +
                    "(i_id,sdate,edate,stime,etime,iroomid,iusrid,istatus,iconfirmbygrpid)" +
                    " values(?,?,?,?,?,?,?,?,?)";
            params = new Object[]{cid, sdate, edate, sStime, sEtime, roomid, usrid, istatus, iconfirmbygrpid};
            types = new int[]{Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER};

        } else {
            sTbName = "t_works";
            sql = "insert into " + sTbName + " " +
                    "(i_id,sdate,edate,stime,etime,iroomid,iusrid,ihost,istatus,iconfirmbygrpid,cinitdept)" +
                    " values(?,?,?,?,?,?,?,?,?,?,?)";
            params = new Object[]{cid, sdate, edate, sStime, sEtime, roomid, usrid, usrid, istatus, iconfirmbygrpid, iconfirmbygrpid};
            types = new int[]{Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER};

        }

        int iRc = jt.update(sql, params, types);

    }

    @Override
    public meetings getMeeting(String cmeetingid, String app) {
        meetings mt = new meetings();
        JdbcTemplate jt = getJdbcTemplate();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        SqlRowSet rs = null;
        String sql = null;
        Object[] params;
        int[] types;
        String sTbName;
        int itmp = 0;
        if (app.equalsIgnoreCase("room")) {
            sTbName = "v_meetings";
            //取出会议的信息
            sql = "select i_id,sdate,edate,cname,roomname," +
                    "deptname,hostname,cabstract,copinion," +
                    "cnotice,inetdown,ivideo,cinitdept,iroomid,ihost," +
                    "iconfirmid,istatus,iconfirmbygrpid,stime,etime,cstatus,iusrid  " +
                    " from   " + sTbName +
                    " where i_id = ?  ";
            params = new Object[]{cmeetingid};
            types = new int[]{Types.CHAR};
            rs = jt.queryForRowSet(sql, params, types);
            while (rs.next()) {
                mt.setSCid(cmeetingid);
                mt.setDEdate(rs.getDate("edate"));
                mt.setDSdate(rs.getDate("sdate"));
                mt.setSSdate(sdf.format(rs.getDate("sdate")));
                mt.setSEdate(sdf.format(rs.getDate("edate")));
                mt.setSCname(rs.getString("cname"));
                mt.setSRoomName(rs.getString("roomname"));
                mt.setSCinitdept(rs.getString("deptname"));
                mt.setSHostName(rs.getString("hostname"));
                mt.setSCabstract(rs.getString("cabstract"));
                mt.setSCoption(rs.getString("copinion"));
                mt.setSCnotice(rs.getString("cnotice"));
                mt.setINetdown(rs.getInt("inetdown"));
                mt.setIVedio(rs.getInt("ivideo"));
                mt.setIInitdept(rs.getInt("cinitdept"));
                mt.setIRoomid(rs.getInt("iroomid"));
                mt.setIHostid(rs.getInt("ihost"));
                mt.setIConfirmid(rs.getInt("iconfirmid"));
                mt.setIStatus(rs.getInt("istatus"));
                mt.setIConfirmByGrpId(rs.getInt("iconfirmbygrpid"));
                mt.setSStime(rs.getString("stime"));
                mt.setSEtime(rs.getString("etime"));
                mt.setCStatus(rs.getString("cstatus"));
                mt.setIUsrid(rs.getInt("iusrid"));
                itmp = rs.getInt("iusrid");
            }
        } else {
            sTbName = "v_works";
            //取出会议的信息
            sql = "select i_id,sdate,edate,cname,roomname," +
                    "deptname,hostname,cabstract,copinion,copinion2,copinion3," +
                    "cnotice,inetdown,ivideo,cinitdept,iroomid,ihost," +
                    "iconfirmid,iconfirmid2,iconfirmid3,istatus,iconfirmbygrpid,stime,etime," +
                    "cstatus,confirmdate,confirmdate2,confirmdate3,crefmtid,cdocno,ccaseno," +
                    "ccasecause,csuspect,csuspnum,csuspatt,csuspdisease,cacceptor,csuspsex,iusrid  " +
                    " from   " + sTbName +
                    " where i_id = ?  ";
            params = new Object[]{cmeetingid};
            types = new int[]{Types.CHAR};
            rs = jt.queryForRowSet(sql, params, types);
            while (rs.next()) {
                mt.setSCid(cmeetingid);
                mt.setDEdate(rs.getDate("edate"));
                mt.setDSdate(rs.getDate("sdate"));
                mt.setSSdate(sdf.format(rs.getDate("sdate")));
                mt.setSEdate(sdf.format(rs.getDate("edate")));
                mt.setSCname(rs.getString("cname"));
                mt.setSRoomName(rs.getString("roomname"));
                mt.setSCinitdept(rs.getString("deptname"));
                mt.setSHostName(rs.getString("hostname"));
                mt.setSCabstract(rs.getString("cabstract"));
                mt.setSCoption(rs.getString("copinion"));
                mt.setSCoption2(rs.getString("copinion2"));
                mt.setSCoption3(rs.getString("copinion3"));
                mt.setSCnotice(rs.getString("cnotice"));
                mt.setINetdown(rs.getInt("inetdown"));
                mt.setIVedio(rs.getInt("ivideo"));
                mt.setIInitdept(rs.getInt("cinitdept"));
                mt.setIRoomid(rs.getInt("iroomid"));
                mt.setIHostid(rs.getInt("ihost"));
                mt.setIConfirmid(rs.getInt("iconfirmid"));
                mt.setIConfirmid2(rs.getInt("iconfirmid2"));
                mt.setIConfirmid3(rs.getInt("iconfirmid3"));
                mt.setIStatus(rs.getInt("istatus"));
                mt.setIConfirmByGrpId(rs.getInt("iconfirmbygrpid"));
                mt.setSStime(rs.getString("stime"));
                mt.setSEtime(rs.getString("etime"));
                mt.setCStatus(rs.getString("cstatus"));
                mt.setCacceptor(rs.getString("cacceptor"));
                mt.setCcasecause(rs.getString("ccasecause"));
                mt.setCcaseno(rs.getString("ccaseno"));
                mt.setCdocno(rs.getString("cdocno"));
                mt.setConfirmdate(rs.getString("confirmdate"));
                mt.setConfirmdate2(rs.getString("confirmdate2"));
                mt.setConfirmdate3(rs.getString("confirmdate3"));
                mt.setCsuspect(rs.getString("csuspect"));
                mt.setCsuspatt(rs.getString("csuspatt"));
                mt.setCsuspdisease(rs.getString("csuspdisease"));
                mt.setCsuspnum(rs.getString("csuspnum"));
                mt.setCsuspsex(rs.getString("csuspsex"));
                mt.setCrefmtid(rs.getString("crefmtid"));
                mt.setIUsrid(rs.getInt("iusrid"));
                itmp = rs.getInt("iusrid");
            }
        }
        sql = "select irtx_id from t_usrset " +
                " where i_id = ? ";
        params = new Object[]{itmp};
        types = new int[]{Types.INTEGER};
        int iRc = 0;
        iRc = jt.queryForInt(sql, params, types);
        mt.setIrtx_id(iRc);
        return mt;
    }

    @Override
    public void removeTmp(int usrid, String app) {
        String sMtid = null;
        //首先取得临时表的id
        String sTbName;
        if (app.equalsIgnoreCase("room")) {
            sTbName = "t_meetings";
        } else {
            sTbName = "t_works";
        }

        JdbcTemplate jt = getJdbcTemplate();
        String sql = "select i_id from " + sTbName + " " +
                "where istatus=0 and iusrid=? ";
        Object[] params = new Object[]{usrid};
        int[] types = new int[]{Types.INTEGER};
        SqlRowSet rs = jt.queryForRowSet(sql, params, types);
        if (rs.first()) {
            sMtid = rs.getString(1);
            sql = "delete from t_attendee " +
                    "where cmeetingid = ? ";
            params = new Object[]{sMtid};
            types = new int[]{Types.CHAR};
            jt.update(sql, params, types);
        }
        sql = "delete from " + sTbName + " " +
                "where istatus=0 and iusrid=? ";
        params = new Object[]{usrid};
        types = new int[]{Types.INTEGER};
        int iRc = jt.update(sql, params, types);



    }

    @Override
    public void insertMTUsr(int usrid, String meetingid) {
        JdbcTemplate jt = getJdbcTemplate();
        //首先删除该条记录，如果有的话
        String sql = "delete from  t_attendee " +
                "where cmeetingid = ? and iusrid = ? ";
        Object[] params = new Object[]{meetingid, usrid};
        int[] types = new int[]{Types.CHAR, Types.INTEGER};
        int iRc = jt.update(sql, params, types);

        //插入一个人员
        sql = "insert into  t_attendee(cmeetingid,iusrid) " +
                "values(?,?) ";
        params = new Object[]{meetingid, usrid};
        types = new int[]{Types.CHAR, Types.INTEGER};
        iRc = jt.update(sql, params, types);

    }

    @Override
    public void removeMTUsr(int usrid, String meetingid) {
        JdbcTemplate jt = getJdbcTemplate();
        //首先删除该条记录，如果有的话
        String sql = "delete from  t_attendee " +
                "where cmeetingid = ? and iusrid = ? ";
        Object[] params = new Object[]{meetingid, usrid};
        int[] types = new int[]{Types.CHAR, Types.INTEGER};
        int iRc = jt.update(sql, params, types);
    }

    @Override
    public void insertMTAllUsr(String meetingid) {
        JdbcTemplate jt = getJdbcTemplate();
        //删除所有记录，如果有的话
        String sql = "delete from  t_attendee " +
                "where cmeetingid = ?  ";
        Object[] params = new Object[]{meetingid};
        int[] types = new int[]{Types.CHAR};
        int iRc = jt.update(sql, params, types);
        //插入所有人员
        sql = "insert into t_attendee(cmeetingid,iusrid)" +
                "select '" + meetingid + "' ,i_id from t_usrset";
        iRc = jt.update(sql);

    }

    @Override
    public void removeMTAllUsr(String meetingid) {
        JdbcTemplate jt = getJdbcTemplate();
        //删除所有记录，如果有的话
        String sql = "delete from  t_attendee " +
                "where cmeetingid = ?  ";
        Object[] params = new Object[]{meetingid};
        int[] types = new int[]{Types.CHAR};
        int iRc = jt.update(sql, params, types);
    }

    @Override
    public void updateMeeting(String cid, int ihost, int initdept, String cname, String cabstract, String cnotice, int istatus, int iusrid, int iroomsort, int iconfirmbygrpid) {
        //更新会议其余信息
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "";
        Object[] params = null;
        int[] types = null;
        if ((iroomsort == 2) && (istatus == 5)) {
            //先到先得类型，直接将批准人设定为申请用户
            sql = "update t_meetings " +
                    " set ihost     = ?," +
                    "     cinitdept = ?," +
                    "     cname     = ?," +
                    "     cabstract = ?," +
                    "     cnotice   = ?," +
                    "     copinion  = '同意'," +
                    "     iconfirmid   = ?," +
                    "     istatus   = ?" +
                    " where i_id = ? ";
            params = new Object[]{ihost, initdept, cname, cabstract, cnotice, iusrid, istatus, cid};
            types = new int[]{Types.INTEGER, Types.INTEGER, Types.CHAR, Types.CHAR, Types.CHAR, Types.INTEGER, Types.INTEGER, Types.CHAR};
        } else {
            sql = "update t_meetings " +
                    " set ihost     = ?," +
                    "     cinitdept = ?," +
                    "     cname     = ?," +
                    "     cabstract = ?," +
                    "     cnotice   = ?," +
                    "     iconfirmbygrpid   = ?," +
                    "     istatus   = ?" +
                    " where i_id = ? ";
            params = new Object[]{ihost, initdept, cname, cabstract, cnotice, iconfirmbygrpid, istatus, cid};
            types = new int[]{Types.INTEGER, Types.INTEGER, Types.CHAR, Types.CHAR, Types.CHAR, Types.INTEGER, Types.INTEGER, Types.CHAR};

        }
        int iRc = jt.update(sql, params, types);
    }

    @Override
    public int getRoomSort(int iroomid) {
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "select i_bj from t_roomset " +
                " where i_id = ? ";
        Object[] params = new Object[]{iroomid};
        int[] types = new int[]{Types.INTEGER};
        int iRc = jt.queryForInt(sql, params, types);
        return iRc;
    }

    @Override
    public void sendRTX(meetings mt, int bj, setupParam stp) {
        //bj为发送消息的类型
        //bj=1  为插入
        //bj=-1 为删除
        //bj=3  为技术科确认等待办公室确认
        //bj=4  为否决该会议申请
        //bj=5  开门信息
        //bj=6  工作区
        //bj=-6 取消工作区申请
        //bj=7  全程录像
        //bj=-7 取消全程录像
        //bj=8  指挥中心
        //bj=-8 取消指挥中心
        //bj=9  网上派警
        //bj=-9 取消网上派警
        //bj=100 断网通知
        int bgs = 0; //办公室
        int jsk = 0; //技术科
        int kmr = 0; //开门人
        int fjd = 0; //法警队长
        int jcz = 0; //主管检察长
        int qclx = 0;//全程录像人员
        int zbfj = 0;//值班法警
        int ncfj = 0;//内勤法警
        String url = "";

        bgs = stp.getBgs();
        jsk = stp.getJsk();
        kmr = stp.getKmr();
        fjd = stp.getFjd();
        jcz = stp.getJcz();
        qclx = stp.getQclx();
        zbfj = stp.getZbfj();
        ncfj = stp.getNcfj();
        url = stp.getUrl();
        String cuuid;
        String cname;
        String cdept;
        String sDate;
        String eDate;
        String cabstract;
        String cacceptor;

        String chost;
        int istatus;
        int iusrid;
        int icfmgrp;

        cuuid = mt.getSCid();
        cname = mt.getSCname();
        sDate = mt.getSSdate();
        eDate = mt.getSEdate();
        istatus = mt.getIStatus();
        cdept = mt.getSRoomName();
        iusrid = mt.getIrtx_id();
        icfmgrp = mt.getIConfirmByGrpId();
        cabstract = mt.getSCabstract();
        cacceptor = mt.getCacceptor();
        chost = mt.getSHostName();
        JdbcTemplate jt = getJdbcTemplate();
        String sql = null;
        String sRev = "", sMsg = null;
        rtxSender rtx = new rtxSender();
        int li_rtxid = 0;
        if (Math.abs(bj) == 1) {
            switch (istatus) {
                case 1:
                    //等待技术科审批
                    //给申请人发消息
                    sMsg = "您好，您申请的会议已确认，正在等待办公室审批...";
                    sRev = iusrid + "";
                    rtx.rtxSend(sRev, sMsg, url);
                    //取出技术科所有有审批权限的人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
                    sMsg = "您好，有一个未审批的会议申请，请及时审批...";
                    sql = "select irtx_id from v_grpusrs where grp_id = " + jsk + " and grp_name <> ? and ileader=1";
                    break;
                case 2:
                    //等待办公室审批
                    //给申请人发消息
                    sMsg = "您好，您已成功提出会议申请，正在等待办公室审批...";
                    sRev = iusrid + "";
                    rtx.rtxSend(sRev, sMsg, url);
                    //取出所有办公室有审批权限的人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
                    sMsg = "您好，有一个未审批的会议申请，请及时审批...";
                    sql = "select irtx_id from v_grpusrs where grp_id = " + bgs + " and grp_name <> ? and ileader=1";
                    break;
                case 5:
                    //已经审批，给所有参会人员发消息
                    sql = "select irtx_id from v_attendee where cmeetingid = ? ";
                    if (bj == 1) {
                        sMsg = "您好，请于" + sDate + "准时在 " + cdept + " 参加会议：" + cname + " 为盼。";
                    } else {
                        sMsg = "您好，原定于" + sDate + "在 " + cdept + " 召开的会议：" + cname + " 已经取消，请注意！";
                    }
                    break;
                default:
                    return;
            }
        }
        //为技术科确认等待办公室确认
        if (bj == 3) {
            //等待办公室审批
            //给申请人发消息
            sMsg = "您好，您已成功提出会议申请，正在等待办公室审批...";
            sRev = iusrid + "";
            rtx.rtxSend(sRev, sMsg, url);
            //取出所有办公室有审批权限的人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
            sMsg = "您好，有一个未审批的会议申请，请及时审批...";
            sql = "select irtx_id from v_grpusrs where grp_id = " + bgs + " and grp_name <> ? and ileader=1";
        }
        //否决会议
        if (bj == 4) {
            //等待办公室审批
            //给申请人发消息
            sMsg = "您好，您提出的申请:" + sDate + " " + cname + " 已经被否决，具体情况请联系相应主管部门...";
            sRev = iusrid + "";
            rtx.rtxSend(sRev, sMsg, url);
            return;
        }
        //为先到先得的会议室发布开门信息
        if (bj == 5) {
            //取出所有开门人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
            sMsg = "您好，因有会议召开，请于" + sDate + "准时将 " + cdept + " 的门锁打开为盼。";
            sql = "select irtx_id from v_grpusrs where grp_id = " + kmr + " and grp_name <> ?";
        }
        //工作区申请
        if (Math.abs(bj) == 6) {
            switch (istatus) {
                case 1:
                    //等待本科室审批
                    //给申请人发消息
                    sMsg = "您好，您申请使用工作区已确认，正在等待科室审批...";
                    sRev = iusrid + "";
                    rtx.rtxSend(sRev, sMsg, url);
                    //取出技术科所有有审批权限的人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
                    sMsg = "您好，有一个未审批的使用工作区申请，请及时审批...";
                    sql = "select irtx_id from v_grpusrs where grp_id = " + icfmgrp + " and grp_name <> ? and ileader=1";
                    break;
                case 8:
                    //等待法警队长审批
                    //给申请人发消息
                    sMsg = "您好，您提出的使用工作区申请已被所在科室确认，正在等待法警队长审批...";
                    sRev = iusrid + "";
                    rtx.rtxSend(sRev, sMsg, url);
                    //取出所有法警队长有审批权限的人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
                    sMsg = "您好，有一个未审批的使用工作区申请，请及时审批...";
                    sql = "select irtx_id from v_grpusrs where grp_id = " + fjd + " and grp_name <> ? and ileader=1";
                    break;
                case 5:
                    //已经审批，给所有参会人员发消息
                    sql = "select iusrid from t_works where i_id = ? ";
                    if (bj == 6) {
                        sMsg = "您好，您要求于" + sDate + "使用工作区的申请已经通过审批。";
                    } else {
                        sMsg = "您好，您要求于" + sDate + "使用工作区的申请已经被取消，请注意！";
                    }
                    break;
                default:
                    return;
            }
        }

        //全程录像
        if (Math.abs(bj) == 7) {
            switch (istatus) {
                case 1:
                    //等待本科室审批
                    //给申请人发消息
                    sMsg = "您好，您申请的全程录像已确认，正在等待科室审批...";
                    sRev = iusrid + "";
                    rtx.rtxSend(sRev, sMsg, url);
                    //取出技术科所有有审批权限的人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
                    sMsg = "您好，有一个未审批的全程录像申请，请及时审批...";
                    sql = "select irtx_id from v_grpusrs where grp_id = " + icfmgrp + " and grp_name <> ? and ileader = 1";
                    break;
                case 6:
                    //等待技术科审批
                    //给申请人发消息
                    sMsg = "您好，您提出的全场录像申请已被所在科室确认，正在等待技术科审批...";
                    sRev = iusrid + "";
                    rtx.rtxSend(sRev, sMsg, url);
                    //取出所有技术科有审批权限的人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
                    sMsg = "您好，有一个未审批的全程录像申请，请及时审批...";
                    sql = "select irtx_id from v_grpusrs where grp_id = " + jsk + " and grp_name <> ? and ileader=1";
                    break;
                case 5:
                    //已经审批，给所有参会人员发消息
                    sql = "select iusrid from t_works where i_id = ? ";
                    if (bj == 7) {
                        sMsg = "您好，您要求于" + sDate + "由技术科提供全程录像的申请已经通过审批。";
                    } else {
                        sMsg = "您好，您要求于" + sDate + "由技术科提供全程录像的申请已经被取消，请注意！";
                    }
                    break;
                default:
                    return;
            }
        }
        //指挥中心
        if (Math.abs(bj) == 8) {
            switch (istatus) {
                case 1:
                    //等待本科室审批
                    //给申请人发消息
                    sMsg = "您好，您申请的使用侦查指挥中心已确认，正在等待科室审批...";
                    sRev = iusrid + "";
                    rtx.rtxSend(sRev, sMsg, url);
                    //取出技术科所有有审批权限的人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
                    sMsg = "您好，有一个未审批的使用侦查指挥中心申请，请及时审批...";
                    sql = "select irtx_id from v_grpusrs where grp_id = " + icfmgrp + " and grp_name <> ? and ileader=1";
                    break;
                case 6:
                    //等待技术科审批
                    //给申请人发消息
                    sMsg = "您好，您提出的使用侦查指挥中心申请已被所在科室确认，正在等待技术科审批...";
                    sRev = iusrid + "";
                    rtx.rtxSend(sRev, sMsg, url);
                    //取出所有技术科有审批权限的人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
                    sMsg = "您好，有一个未审批的使用侦查指挥中心的申请，请及时审批...";
                    sql = "select irtx_id from v_grpusrs where grp_id = " + jsk + " and grp_name <> ? and ileader=1";
                    break;
                case 7:
                    //等待主管检察长审批
                    //给申请人发消息
                    sMsg = "您好，您提出的使用侦查指挥中心申请已被技术科确认，正在等待主管检察长审批...";
                    sRev = iusrid + "";
                    rtx.rtxSend(sRev, sMsg, url);
                    //取出所有主管检察长有审批权限的人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
                    sMsg = "您好，有一个未审批的使用侦查指挥中心的申请，请及时审批...";
                    sql = "select irtx_id from v_grpusrs where grp_id = " + jcz + " and grp_name <> ? and ileader=1";
                    break;

                case 5:
                    //已经审批，给所有参会人员发消息
                    sql = "select iusrid from t_works where i_id = ? ";
                    if (bj == 8) {
                        sMsg = "您好，您要求于" + sDate + "使用侦查指挥中心的申请已经通过审批。";
                    } else {
                        sMsg = "您好，您要求于" + sDate + "使用侦查指挥中心的申请已经被取消，请注意！";
                    }
                    break;
                default:
                    return;
            }
        }
        //网上派警
        if (Math.abs(bj) == 9) {
            switch (istatus) {
                case 1:
                    //等待本科室审批
                    //给申请人发消息
                    sMsg = "您好，您申请的网上派警已确认，正在等待科室审批...";
                    sRev = iusrid + "";
                    rtx.rtxSend(sRev, sMsg, url);
                    //取出技术科所有有审批权限的人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
                    sMsg = "您好，有一个未审批的网上派警申请，请及时审批...";
                    sql = "select irtx_id from v_grpusrs where grp_id = " + icfmgrp + " and grp_name <> ? and ileader=1";
                    break;
                case 8:
                    //等待法警队审批
                    //给申请人发消息
                    sMsg = "您好，您提出的网上派警申请已被所在科室确认，正在等待法警队审批...";
                    sRev = iusrid + "";
                    rtx.rtxSend(sRev, sMsg, url);
                    //取出所有技术科有审批权限的人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
                    sMsg = "您好，有一个未审批的使用网上派警的申请，请及时审批...";
                    sql = "select irtx_id from v_grpusrs where grp_id = " + fjd + " and grp_name <> ? and ileader=1";
                    break;
                case 9:
                    //等待主管检察长审批
                    //给申请人发消息
                    sMsg = "您好，您提出的使用网上派警申请已被法警队确认，正在等待主管检察长审批...";
                    sRev = iusrid + "";
                    rtx.rtxSend(sRev, sMsg, url);
                    //取出所有主管检察长有审批权限的人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
                    sMsg = "您好，有一个未审批的使用网上派警的申请，请及时审批...";
                    sql = "select irtx_id from v_grpusrs where grp_id = " + jcz + " and grp_name <> ? and ileader=1";
                    break;

                case 5:
                    //给申请人发通知
                    if (bj == 9) {
                        sMsg = "您好，您要求于" + sDate + "使用网上派警的申请已经通过审批。";
                    } else {
                        sMsg = "您好，您要求于" + sDate + "使用网上派警的申请已经被取消，请注意！";
                    }
                    sRev = iusrid + "";
                    rtx.rtxSend(sRev, sMsg, url);
                    //已经审批，给所有参会人员发消息
                    sMsg = "您好，请您于" + sDate + "携带（" + cabstract + "）和相关法律文书，前往（" + cname + "）协助（" + chost + "）完成（" + cacceptor + "）";
                    sql = "select irtx_id from v_attendee where cmeetingid = ? ";
                    break;
                default:
                    return;
            }
        }
        //为确认过的工作区申请，通知到开门法警队组别
        if (bj == 16) {
            //取出所有开门人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
            sMsg = "您好，因有已确认的工作区使用申请，请于" + sDate + "准时将 " + cdept + " 的门锁打开为盼。";
            sql = "select irtx_id from v_grpusrs where grp_id = " + zbfj + " and grp_name <> ?";
        }
        //为确认过的全程录像通知到执行人组别
        if (bj == 17) {
            //取出所有开门人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
            sMsg = "您好，因有已确认的全程录像申请，请于" + sDate + "准时在 " + cdept + " 进行全程录像操作为盼。";
            sql = "select irtx_id from v_grpusrs where grp_id = " + qclx + " and grp_name <> ?";
        }
        //断网通知
        if (bj == 100) {
            //取出所有的人，给他们发消息，后面的的那个？其实是没有用处的，就是为了同意后面types等等的写法而已
            sMsg = mt.getSCnotice();
            sql = "select irtx_id from v_grpusrs where grp_name <> ?";
        }

        Object[] params = new Object[]{cuuid};
        int[] types = new int[]{Types.CHAR};
        SqlRowSet rs = jt.queryForRowSet(sql, params, types);
        //清空以前的变量
        sRev = "";
        while (rs.next()) {
            li_rtxid = rs.getInt(1);
            sRev += li_rtxid + ",";
        }
        if (sRev.length() > 0) {
            sRev = sRev.substring(0, (sRev.length() - 1));
        }
        //System.out.println(sMsg+sRev);
        rtx.rtxSend(sRev, sMsg, url);

    }

    @Override
    public String getGrpName(int igrpid) {
        JdbcTemplate jt = getJdbcTemplate();
        String sName = null;
        String sql = "select cname from t_grpset " +
                " where i_id = ? ";
        Object[] params = new Object[]{igrpid};
        int[] types = new int[]{Types.INTEGER};
        SqlRowSet rs = jt.queryForRowSet(sql, params, types);
        if (rs.first()) {
            sName = rs.getString(1);
        }

        return sName;
    }

    @Override
    public String getRoomName(int iroomid) {
        JdbcTemplate jt = getJdbcTemplate();
        String sName = null;
        String sql = "select cname from t_roomset " +
                " where i_id = ? ";
        Object[] params = new Object[]{iroomid};
        int[] types = new int[]{Types.INTEGER};
        SqlRowSet rs = jt.queryForRowSet(sql, params, types);
        if (rs.first()) {
            sName = rs.getString(1);
        }
        return sName;
    }

    @Override
    public int getMeetingCnt(String sdate, String edate, int roomid, String app) {
        String sTbName;
        if (app.equalsIgnoreCase("room")) {
            sTbName = "t_meetings";
        } else {
            sTbName = "t_works";
        }

        JdbcTemplate jt = getJdbcTemplate();
        String sql = "select count(*) as cnt from " + sTbName + " " +
                " where istatus = 5 and iroomid = ? and  ((sdate = ? or edate=?) or " +
                "(sdate > ? and sdate < ? ) or " +
                "(sdate < ? and edate > ?) or " +
                "(edate >? and edate <?) or  " +
                "(sdate > ? and edate < ?)) ";
        Object[] params = new Object[]{roomid, sdate, edate,
            sdate, edate,
            sdate, edate,
            sdate, edate,
            sdate, edate};
        int[] types = new int[]{Types.INTEGER, Types.CHAR, Types.CHAR,
            Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR};
        int iRc = jt.queryForInt(sql, params, types);
        return iRc;
    }

    @Override
    public void removeMeeting(String meetingid, String app) {
        JdbcTemplate jt = getJdbcTemplate();
        //删除所有记录，如果有的话
        //删除参会者
        String sql = "delete from  t_attendee " +
                "where cmeetingid = ?  ";
        Object[] params = new Object[]{meetingid};
        int[] types = new int[]{Types.CHAR};
        int iRc = jt.update(sql, params, types);
        String sTbName;
        if (app.equalsIgnoreCase("room")) {
            sTbName = "t_meetings";
        } else {
            sTbName = "t_works";
        }
        //删除会议记录
        sql = "delete from  " + sTbName +
                " where i_id = ?  ";
        iRc = jt.update(sql, params, types);

    }

    @Override
    public int getMeetingStatus(String meetingid) {
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "select istatus from t_meetings " +
                " where i_id = ? ";
        Object[] params = new Object[]{meetingid};
        int[] types = new int[]{Types.CHAR};
        int iRc = jt.queryForInt(sql, params, types);
        return iRc;
    }

    @Override
    public int getMeetingCnt(String meetingid) {
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "select count(*) cnt from t_meetings " +
                " where istatus=5 and i_id = ?  ";
        Object[] params = new Object[]{meetingid};
        int[] types = new int[]{Types.CHAR};
        int iRc = jt.queryForInt(sql, params, types);
        return iRc;
    }

    @Override
    public String getGrpId(int iusrid) {
        JdbcTemplate jt = getJdbcTemplate();
        int igrpid;
        int i = 0;
        String sTmp = "";
        String sql = "select igrpid from t_grpusrs " +
                " where iusrid = ? and ileader=1";
        Object[] params = new Object[]{iusrid};
        int[] types = new int[]{Types.INTEGER};
        SqlRowSet rs = jt.queryForRowSet(sql, params, types);
        while (rs.next()) {
            igrpid = rs.getInt(1);
            sTmp += igrpid + ",";
            i++;
        }
        if (i > 0) {
            sTmp = sTmp.substring(0, (sTmp.length() - 1));
        }
        return sTmp;
    }

    @Override
    public int getRoomConfirmGrpId(int iroomid) {
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "select igrpid from t_roomset " +
                " where i_id = ? ";
        Object[] params = new Object[]{iroomid};
        int[] types = new int[]{Types.INTEGER};
        int iRc = jt.queryForInt(sql, params, types);
        return iRc;
    }

    @Override
    public int getRoomConfirmGrpIdByUser(int iusrid) {
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "select igrpid from v_usr " +
                " where i_id = ? and igrpsort=1 ";
        Object[] params = new Object[]{iusrid};
        int[] types = new int[]{Types.INTEGER};
        int iRc = jt.queryForInt(sql, params, types);
        return iRc;
    }

    @Override
    public void confirmMeeting(String cid, String copinion, int istatus, int iconfirmid, int ibj) {
        //确认一个会议,bj=0 为办公室确认 bj=1为科室确认
        int bgs = 19; //办公室 zz2010

        JdbcTemplate jt = getJdbcTemplate();
        String sql;
        Object[] params;
        int[] types;
        if (ibj == 0) {
            //办公室直接确认了
            sql = "update t_meetings " +
                    " set copinion     = ?," +
                    "     iconfirmid   = ?," +
                    "     istatus   = ? " +
                    " where i_id = ? ";
            params = new Object[]{copinion, iconfirmid, istatus, cid};
            types = new int[]{Types.CHAR, Types.INTEGER, Types.INTEGER, Types.CHAR};
        } else {
            //将要确认的组别号码设置为办公室的编号
            sql = "update t_meetings " +
                    " set copinion     = ?," +
                    "     istatus   = ?," +
                    "     iconfirmbygrpid  = " + bgs +
                    " where i_id = ? ";
            params = new Object[]{copinion, istatus, cid};
            types = new int[]{Types.CHAR, Types.INTEGER, Types.CHAR};
        }

        int iRc = jt.update(sql, params, types);
    }

    @Override
    public String getAttendeeList(String cid) {
        JdbcTemplate jt = getJdbcTemplate();
        int igrpid;
        String sName = "";
        int i = 0;
        String sTmp = "";
        String sql = "select name2 from v_attendee " +
                " where cmeetingid = ? and igrpsort=1";
        Object[] params = new Object[]{cid};
        int[] types = new int[]{Types.CHAR};
        SqlRowSet rs = jt.queryForRowSet(sql, params, types);
        while (rs.next()) {
            sName = rs.getString(1);
            sTmp += sName + ",";
            i++;
        }
        if (i > 0) {
            sTmp = sTmp.substring(0, (sTmp.length() - 1));
        }
        return sTmp;
    }

    @Override
    public void vetoMeeting(String cid, String copinion, int istatus, int iconfirmid, int ibj, String app) {
        //否决一个申请
        String sTbName;
        if (app.equalsIgnoreCase("room")) {
            sTbName = "t_meetings";
        } else {
            sTbName = "t_works";
        }

        JdbcTemplate jt = getJdbcTemplate();
        String sql;
        sql = "update  " + sTbName +
                " set copinion     = ?," +
                "     iconfirmid   = ?," +
                "     istatus   = ?" +
                " where i_id = ? ";
        Object[] params = new Object[]{copinion, iconfirmid, istatus, cid};
        int[] types = new int[]{Types.CHAR, Types.INTEGER, Types.INTEGER, Types.CHAR};

        int iRc = jt.update(sql, params, types);
    }

    @Override
    public int getGrpid(int usrid, int grpid, int leader) {
        JdbcTemplate jt = getJdbcTemplate();
        String sql;
        Object[] params;
        int[] types;
        if (leader < 0) {
            //全体人员
            sql = "select count(*) as cnt from t_grpusrs " +
                    " where iusrid= ? and igrpid = ?  ";
            params = new Object[]{usrid, grpid};
            types = new int[]{Types.INTEGER, Types.INTEGER};
        } else {
            //0为普通1为领导
            sql = "select count(*) as cnt from t_grpusrs " +
                    " where iusrid= ? and igrpid = ? and ileader = ? ";
            params = new Object[]{usrid, grpid, leader};
            types = new int[]{Types.INTEGER, Types.INTEGER, Types.INTEGER};
        }
        int iRc = jt.queryForInt(sql, params, types);
        return iRc;
    }

    @Override
    public int getGrpId(int iusrid, int ibj) {
        JdbcTemplate jt = getJdbcTemplate();
        int igrpid = 0;
        String sql = "select igrpid from v_usr " +
                " where i_id = ? and igrpsort = ?";
        Object[] params = new Object[]{iusrid, ibj};
        int[] types = new int[]{Types.INTEGER, Types.INTEGER};
        SqlRowSet rs = jt.queryForRowSet(sql, params, types);
        while (rs.next()) {
            igrpid = rs.getInt(1);
        }
        return igrpid;
    }

    @Override
    public void updateTimeNroom(String cid, String sdate, String edate, String stime, String etime, int roomid, String app) {
        //更新会议其余信息
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "";
        Object[] params = null;
        int[] types = null;
        String sTbName;
        if (app.equalsIgnoreCase("room")) {
            sTbName = "t_meetings";
        } else {
            sTbName = "t_works";
        }

        //先到先得类型，直接将批准人设定为申请用户
        sql = "update  " + sTbName +
                " set sdate = ?," +
                "     edate = ?," +
                "     stime = ?," +
                "     etime = ?," +
                "     iroomid   = ?" +
                " where i_id = ? ";
        params = new Object[]{sdate, edate, stime, etime, roomid, cid};
        types = new int[]{Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.INTEGER, Types.CHAR};
        int iRc = jt.update(sql, params, types);
    }

    @Override
    public String[] getRoomStat(int roomid, String sdate, String app) {
        String[] sRc = new String[48];
        String sTmp = "";
        String sDt, eDt, sTm, eTm;

        int istat = 0;
        int i = 0;
        int j = 0;
        String ls_tmp = "";
        JdbcTemplate jt = getJdbcTemplate();
        Object[] params = null;
        int[] types = null;
        String sTbName;
        if (app.equalsIgnoreCase("room")) {
            sTbName = "t_meetings";
        } else {
            sTbName = "t_works";
        }
        String sql = "SELECT i_id, sdate,stime," +
                " edate, etime, istatus  " +
                "FROM " + sTbName + " WHERE istatus > 0 and iroomid = ?   " +
                "AND ( ( date_format(sdate  ,\'%Y-%m-%d\')  = ? or " +
                "date_format(edate,\'%Y-%m-%d\')  = ? )) order by sdate";
        params = new Object[]{roomid, sdate, sdate};
        types = new int[]{Types.INTEGER, Types.CHAR, Types.CHAR};
        SqlRowSet rs = jt.queryForRowSet(sql, params, types);
        Date ldt_sdate = null, ldt_sd = null;
        Date ldt_ed = null;
        String ls_tmp2;
        while (rs.next()) {
            sDt = rs.getString(2);
            sTm = rs.getString(3);
            eDt = rs.getString(4);
            eTm = rs.getString(5);
            istat = rs.getInt(6);

            for (i = 0; i < 48; i++) {
                if (app.equalsIgnoreCase("room")) {
                    //改为7am-7pm
                    if (i < 14 || i > 38) {
                        continue;
                    }
                }
                if (i < 20) {
                    if (i % 2 == 0) {
                        ls_tmp = "0" + i / 2 + ":00";
                    } else {
                        ls_tmp = "0" + i / 2 + ":30";
                    }
                } else {
                    if (i % 2 == 0) {
                        ls_tmp = i / 2 + ":00";
                    } else {
                        ls_tmp = i / 2 + ":30";
                    }
                }
                ls_tmp2 = sdate + " " + ls_tmp + ":00";
                SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                try {
                    //传入的那个查询时间+拼凑的串,从早7点到晚7点
                    ldt_sdate = sdf2.parse(ls_tmp2);
                    //数据库中的开始时间
                    ldt_sd = sdf2.parse(sDt);
                    ldt_ed = sdf2.parse(eDt);
                } catch (ParseException ex) {
                    Logger.getLogger(main.class.getName()).log(Level.SEVERE, null, ex);
                }
                if (ldt_sdate.getTime() >= ldt_sd.getTime()) {
                    if (ldt_sdate.getTime() > ldt_ed.getTime()) {
                        break;
                    }
                    sTmp = istat + "-" + ls_tmp;
                    sRc[j] = sTmp;
                    j++;
                }
                //cls
                ls_tmp = "";
            }
        }
        return sRc;
    }

    @Override
    public void updateMeeting(String cid, int ihost, int initdept, String cname, String cabstract, String cnotice, int istatus, int iusrid, int iroomsort, int iconfirmbygrpid, int inetdown) {
        //更新会议其余信息
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "";
        Object[] params = null;
        int[] types = null;
        //视频测试，直接将批准人设定为申请用户
        sql = "update t_meetings " +
                " set ihost     = ?," +
                "     cinitdept = ?," +
                "     cname     = ?," +
                "     cabstract = ?," +
                "     cnotice   = ?," +
                "     copinion  = '同意'," +
                "     iconfirmid   = ?," +
                "     istatus   = ?," +
                "     inetdown   = ?" +
                " where i_id = ? ";
        params = new Object[]{ihost, initdept, cname, cabstract, cnotice, iusrid, istatus, inetdown, cid};
        types = new int[]{Types.INTEGER, Types.INTEGER, Types.CHAR, Types.CHAR, Types.CHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.CHAR};
        int iRc = jt.update(sql, params, types);
    }

    @Override
    public void resolveConflict(String sdate, String edate, int roomid) {
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "update t_meetings set istatus=3" +
                " where istatus = 5 and iroomid = ? and  ((sdate = ? or edate=?) or " +
                "(sdate > ? and sdate < ? ) or " +
                "(sdate < ? and edate > ?) or " +
                "(edate >? and edate <?) or  " +
                "(sdate > ? and edate < ?)) ";
        Object[] params = new Object[]{roomid, sdate, edate,
            sdate, edate,
            sdate, edate,
            sdate, edate,
            sdate, edate};
        int[] types = new int[]{Types.INTEGER, Types.CHAR, Types.CHAR,
            Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR};
        int iRc = jt.update(sql, params, types);
        return;
    }

    @Override
    public void updateMeeting(String cid, int ivideo) {
        //更新会议其余信息
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "";
        Object[] params = null;
        int[] types = null;
        //是否为视频会议
        sql = "update t_meetings " +
                " set ivideo     = ?" +
                " where i_id = ? ";
        params = new Object[]{ivideo, cid};
        types = new int[]{Types.INTEGER, Types.CHAR};
        int iRc = jt.update(sql, params, types);
    }

    @Override
    public void updateWork(works lwks) {
        //更新会议其余信息
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "";
        Object[] params = null;
        int[] types = null;
        //更新works的一些详细内容
        sql = "update t_works " +
                " set istatus     = ?," +
                "     cinitdept = ?," +
                "     ihost     = ?," +
                "     cname     = ?," +
                "     cabstract = ?," +
                "     cnotice   = ?," +
                "     crefmtid  = ?," +
                "     cdocno    = ?," +
                "     ccaseno   = ?," +
                "     ccasecause = ?," +
                "     csuspect   = ?," +
                "     csuspnum   = ?," +
                "     csuspatt   = ?," +
                "     csuspdisease  = ?," +
                "     csuspsex  = ?," +
                "     cacceptor     = ?" +
                " where i_id = ? ";
        params = new Object[]{lwks.getIstatus(), lwks.getCinitdept(), lwks.getIhost(), lwks.getCname(), lwks.getCabstract(), lwks.getCnotice(), lwks.getCrefmtid(), lwks.getCdocno(), lwks.getCcaseno(), lwks.getCcasecause(), lwks.getCsuspect(), lwks.getCsuspnum(), lwks.getCsuspatt(), lwks.getCsuspdisease(), lwks.getCsuspsex(), lwks.getCacceptor(), lwks.getI_id()};
        types = new int[]{Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR, Types.CHAR};
        int iRc = jt.update(sql, params, types);

    }

    @Override
    public void confirmWork(String cid, String copinion, int istatus, int iconfirmbygrpid, int ibj, int iusrid) {
        //确认一个工作区,bj=0 为本科室确认 bj=1 为技术科确认 bj=2 为法警队确认 bj=3 为检察长确认
        JdbcTemplate jt = getJdbcTemplate();
        String sql = null;
        Object[] params = null;
        int[] types = null;
        //本科室自己确认
        if (ibj == 0) {
            //将要确认的组别号码设置为办公室的编号
            sql = "update t_works " +
                    " set copinion3     = ?," +
                    "     istatus   = ?," +
                    "     iconfirmid3   = ?," +
                    "     iconfirmbygrpid  = ?, " +
                    "     confirmdate3   = now()" +
                    " where i_id = ? ";
            params = new Object[]{copinion, istatus, iusrid, iconfirmbygrpid, cid};
            types = new int[]{Types.CHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.CHAR};
        } else {
            if (istatus != 5) {
                //仍需继续确认
                if (ibj > 0) {
                    sql = "update t_works " +
                            " set copinion2     = ?," +
                            "     istatus   = ?," +
                            "     iconfirmid2   = ?," +
                            "     iconfirmbygrpid  = ?, " +
                            "     confirmdate2   = now()" +
                            " where i_id = ? ";
                    params = new Object[]{copinion, istatus, iusrid, iconfirmbygrpid, cid};
                    types = new int[]{Types.CHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.CHAR};
                }
            } else {
                //最终确认
                sql = "update t_works " +
                        " set copinion     = ?," +
                        "     istatus   = ?," +
                        "     iconfirmid   = ?," +
                        "     iconfirmbygrpid  = ?," +
                        "     confirmdate   = now()" +
                        " where i_id = ? ";
                params = new Object[]{copinion, istatus, iusrid, iconfirmbygrpid, cid};
                types = new int[]{Types.CHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.CHAR};
            }
        }
        int iRc = jt.update(sql, params, types);
    }

    @Override
    public void confirmWork(String cid, String copinion, int istatus, int iconfirmbygrpid, int ibj, int iusrid, String cabstract) {
        //法警大队长确认一个网上派警
        JdbcTemplate jt = getJdbcTemplate();
        String sql = null;
        Object[] params = null;
        int[] types = null;
        sql = "update t_works " +
                " set copinion2     = ?," +
                "     istatus   = ?," +
                "     iconfirmid2   = ?," +
                "     iconfirmbygrpid  = ?, " +
                "     cabstract   = ?," +
                "     confirmdate2   = now()" +
                " where i_id = ? ";
        params = new Object[]{copinion, istatus, iusrid, iconfirmbygrpid, cabstract, cid};
        types = new int[]{Types.CHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.CHAR, Types.CHAR};
        int iRc = jt.update(sql, params, types);
    }

    @Override
    public String getUsrRealName(int iusrid) {
        JdbcTemplate jt = getJdbcTemplate();
        String sName = null;
        String sql = "select crealname from t_usrset " +
                " where i_id = ? ";
        Object[] params = new Object[]{iusrid};
        int[] types = new int[]{Types.INTEGER};
        SqlRowSet rs = jt.queryForRowSet(sql, params, types);
        if (rs.first()) {
            sName = rs.getString(1);
        }
        return sName;
    }
    //取得工作区的延迟时间

    @Override
    public int getSetup(String cname) {
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "select value from t_setup " +
                " where cname = ? ";
        Object[] params = new Object[]{cname};
        int[] types = new int[]{Types.CHAR};
        int iRc = jt.queryForInt(sql, params, types);
        return iRc;
    }
    //取得传人工作事务对应的派警人数
    //bj=0时对应select count(*) from v_attendee where igrpsort=1 and cmeetingid in (select i_id from t_works where cacceptor=?);
    //bj=1时对应select count(*) from v_attendee where igrpsort=1 and cmeetingid in (select i_id from t_works where cinitdept=?);

    @Override
    public int getAttendeeCnt(String cparam, String sdate, String edate, int ibj) {
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "";
        if (ibj == 0) {
            sql = "select count(*) as cnt from v_attendee where igrpsort=1 and cmeetingid in (select i_id from t_works where cacceptor=? and date_format(sdate  ,\'%Y-%m-%d\')  between ? and ? )";
        } else {
            sql = "select count(*) as cnt from v_attendee where igrpsort=1 and cmeetingid in (select i_id from t_works where cinitdept=? and date_format(sdate  ,\'%Y-%m-%d\')  between ? and ? )";
        }
        Object[] params = new Object[]{cparam, sdate, edate};
        int[] types = new int[]{Types.CHAR, Types.CHAR, Types.CHAR};
        int rs = jt.queryForInt(sql, params, types);
        return rs;
    }
    //取得网上派警统计报表的基础数值
    //bj=0时对应select cacceptor,count(*),sum(csuspatt) from v_odquery group by cacceptor;传人的是cacceptor
    //bj=1时对应select cdept,cinitdept,count(*),sum(csuspatt) from v_odquery group by cdept,cinitdept;传人的为cinitdept

    @Override
    public SqlRowSet getODQuery(String sdate, String edate, int ibj) {
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "";
        if (ibj == 0) {
            sql = "select cacceptor,count(*) as cnt,sum(csuspatt) as zs from v_odquery where date_format(sdate  ,\'%Y-%m-%d\')  between ? and ?  group by cacceptor";
        } else {
            sql = "select cdept,cinitdept,count(*) as cnt,sum(csuspatt) as zs from v_odquery where date_format(sdate  ,\'%Y-%m-%d\')  between ? and ? group by cdept,cinitdept";
        }
        Object[] params = new Object[]{sdate, edate};
        int[] types = new int[]{Types.CHAR, Types.CHAR};
        SqlRowSet rs = jt.queryForRowSet(sql, params, types);
        return rs;
    }

    @Override
    public int getSeqWorkSpace() {
        //得到工作区的编号
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "";
        Object[] params = null;
        int[] types = null;
        //是否为视频会议
        sql = "update t_seq_workspace " +
                " set id = LAST_INSERT_ID(id + 1)  ";
        int iRc = jt.update(sql);
        sql = "select LAST_INSERT_ID()";
        iRc = jt.queryForInt(sql);
        return iRc;
    }

    @Override
    public int getSeqOfficerDispatch() {
        //得到工作区的编号
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "";
        Object[] params = null;
        int[] types = null;
        //是否为视频会议
        sql = "update t_seq_officerdispatch " +
                " set id = LAST_INSERT_ID(id + 1)  ";
        int iRc = jt.update(sql);
        sql = "select LAST_INSERT_ID()";
        iRc = jt.queryForInt(sql);
        return iRc;
    }

    @Override
    public setupParam getParam() {
        setupParam stp = new setupParam();
        int bgs = 0; //办公室
        int jsk = 0; //技术科
        int kmr = 0; //开门人
        int fjd = 0; //法警队长
        int jcz = 0; //主管检察长
        int qclx = 0;//全程录像人员
        int zbfj = 0;//值班法警
        int ncfj = 0;//内勤法警
        String url = ""; //rtx地址
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "";
        sql = "select value from t_setup where cname = '办公室组' ";
        bgs = jt.queryForInt(sql);
        stp.setBgs(bgs);
        sql = "select value from t_setup where cname = '技术科组' ";
        jsk = jt.queryForInt(sql);
        stp.setJsk(jsk);
        sql = "select value from t_setup where cname = '开门人组' ";
        kmr = jt.queryForInt(sql);
        stp.setKmr(kmr);
        sql = "select value from t_setup where cname = '法警队长组' ";
        fjd = jt.queryForInt(sql);
        stp.setFjd(fjd);
        sql = "select value from t_setup where cname = '主管检察长组' ";
        jcz = jt.queryForInt(sql);
        stp.setJcz(jcz);
        sql = "select value from t_setup where cname = '录像人员组' ";
        qclx = jt.queryForInt(sql);
        stp.setQclx(qclx);
        sql = "select value from t_setup where cname = '值班法警组' ";
        zbfj = jt.queryForInt(sql);
        stp.setZbfj(zbfj);
        sql = "select value from t_setup where cname = '内勤法警组' ";
        ncfj = jt.queryForInt(sql);
        stp.setNcfj(ncfj);
        sql = "select value from t_setup where cname = '腾讯RTX服务器地址' ";
        url = String.valueOf(jt.queryForObject(sql, String.class));
        stp.setUrl(url);


        return stp;
    }

    @Override
    public String getNowMeetingId(int roomid, String app) {
        String sTbName;
        String sRc = "";
        if (app.equalsIgnoreCase("room")) {
            sTbName = "t_meetings";
        } else {
            sTbName = "t_works";
        }

        JdbcTemplate jt = getJdbcTemplate();
        String sql = "select count(*) as cnt from " + sTbName + " " +
                " where istatus = 5 and iroomid = ? and  (sdate < now() and edate> now()) ";
        Object[] params = new Object[]{roomid};
        int[] types = new int[]{Types.INTEGER};
        int iRc = jt.queryForInt(sql, params, types);
        if (iRc > 0) {
            sql = "select i_id from " + sTbName + " " +
                    " where istatus = 5 and iroomid = ? and  (sdate < now() and edate> now()) ";
            sRc = String.valueOf(jt.queryForObject(sql, params, types, String.class));
        } else {
            sRc = "";
        }
        return sRc;
    }

    @Override
    public void updateWorkEndTime(String cid, String edate) {
        String sEtime,sql;
        sEtime = edate.substring(11, 16);
         JdbcTemplate jt = getJdbcTemplate();
            sql = "update t_works " +
                    " set edate = ?," +
                    "     etime = ? " +
                    " where i_id = ? ";
            Object[]  params = new Object[]{edate,sEtime,cid};
            int[] types= new int[]{ Types.CHAR,Types.CHAR, Types.CHAR};

        int iRc = jt.update(sql, params, types);

    }
}